Mega Code Archive
PL SQL Programming
1) A complete example using RAISE_APPLICATION_ERROR
2) A PLSQL Block
3) A Safe INSERT Statement
4) A User-Defined Exception
5) A variable is null if a value hasnt been assigned to it yet
6) Add a row to the classes table, using the values of the variables
7) Adding User-Defined Exceptions
8) An example of an anonymous block
9) An example of using PRAGMA EXCEPTION_INIT
10) An example showing continuing program execution after handling exception
11) An example showing handling of pre-defined exceptions
12) Anonymous Block Structure
13) Anonymous blocks can be nested in the procedure and exception blocks in as many levels as you want
14) Assign custom exception a number
15) Assign SQL query results to PLSQL variables
16) Assigning a Name to Predefined Exception Code
17) Assigning values to variables
18) Avoiding exceptions raised in declaration part and exception handler
19) Because OTHERS handles all exceptions, you must list it after any specific exceptions in your EXCEPTION block
20) Call user defined function in DBMS_OUTPUT PUT_LINE
21) Catch cannot get lock exception
22) Catch custom exception
23) Catch divide by zero exception
24) Character and string literals in the Oracle world are enclosed by single quotes
25) Check OTHERS exception
26) Check SQL%NOTFOUND after delete statement
27) Check the result of count aggregation function and then raise exception
28) Code with Conditional Control to Avoid an Exception
29) Code with Explicit Handler for Predefined Exception
30) Code with No Exception Handler
31) Comments
32) Controlling scope with a variable declaration
33) Create your own no_data_found EXCEPTION
34) Data not found exception
35) DBMS_OUTPUT CHARARR
36) DBMS_OUTPUT GET_LINES
37) DBMS_RANDOM
38) DBMS_ROWID ROWID_BLOCK_NUMBER(ROWID)
39) Dbms_stats set_table_stats
40) Dbms_utility get_time
41) DBMS_WARNING package
42) Declare dbms_utility get_time as a variable default value
43) Declare variables in exception handler
44) Declaring a Variable by Reference
45) Declaring variables
46) Delete employee with stored procedure
47) Delete rows using PLSQL literals and variables
48) DELETE statement with variable
49) Delete table data in PLSQL
50) Delimiters
51) Demonstrate nested PLSQL blocks
52) Display error
53) DUP_VAL_ON_INDEX Exception
54) Each complete line of the PLSQL code must end with a semicolon (;)
55) Error message code and text
56) Examples of Integer and Real Literals
57) Exception Handling
58) Exceptions
59) Exceptions Raised in the Exception Handler
60) Get error information
61) Handling a custom exception
62) Handling an Unnamed Exception
63) Handling exceptions without halting the program
64) Handling user-defined exceptions with a WHEN clause
65) How DELETEs work with PLSQL
66) How DML works with PLSQL
67) How INSERTs work with PLSQL
68) In SQL statements, the names of database columns take precedence over the names of local variables and formal parameter
69) Including error messages in user-defined exceptions
70) Inline procedure
71) Inner function
72) Inner procedure in an anonymous function
73) Insert by Using RECORD Type Variable
74) Insert data in PLSQL block
75) Insert value to product and productcategory with stored procedure
76) Insert values using PLSQL literals and variables
77) INVALID_NUMBER Exception
78) Literals as variable values
79) Multi-line comments start with and end with
80) New quoting syntax is q[ ] where the [] is the user-defined delimiter
81) NO data found
82) Numeric literals cannot contain dollar signs or commas, but they can be written in scientific notation
83) ORA_CLIENT_IP_ADDRESS returns the client IP address as a VARCHAR2 datatype
84) ORA_DES_ENCRYPTED_PASSWORD returns the DES-encrypted password as a VARCHAR2 datatype
85) ORA_DICT_OBJ_NAME returns an object name as a VARCHAR2 datatype
86) ORA_DICT_OBJ_NAME_LIST
87) ORA_DICT_OBJ_OWNER returns an owner of the object acted upon by the event as a VARCHAR2 datatype
88) ORA_DICT_OBJ_OWNER_LIST returns the number of elements in the list indexed by a PLS_INTEGER datatype
89) ORA_DICT_OBJ_TYPE returns the datatype of the dictionary object changed by the event as a VARCHAR2 datatype
90) ORA_GRANTEE returns the number of elements in the list indexed by a PLS_INTEGER datatype
91) ORA_INSTANCE_NUM returns the current database instance number as a NUMBER datatype
92) ORA_IS_ALTER_COLUMN returns a true or false value as a BOOLEAN datatype
93) ORA_IS_CREATING_NESTED_TABLE returns a true or false value as a BOOLEAN datatype when you create a table with a nested
94) ORA_LOGIN_USER function returns the current schema name as a VARCHAR2 datatype
95) ORA_PRIVILEGE_LIST returns the number of elements in the list indexed by a PLS_INTEGER datatype
96) ORA_SERVER_ERROR returns an error number as a NUMBER datatype
97) ORA_SERVER_ERROR_MSG returns an error message text as a VARCHAR2 datatype
98) ORA_SYSEVENT function returns the system event that was responsible for firing the trigger as a VARCHAR2 datatype
99) ORA_WITH_GRANT_OPTION returns a true or false value as a BOOLEAN datatype
100) OTHERS Exception
101) Output NUMBER with dbms_output put_line Procedure
102) Output string with dbms_output put_line Procedure
103) PLSQL Block Showing the Use of the dbms_output put_line Procedure
104) Procedure for adjust salary
105) Propagation of Exceptions between Program Units
106) Propogating a Server-side Customized Error Number and Error Message to client program using PRAGMA EXCEPTION_INIT
107) Raise applocation error
108) Raise Exception in a function
109) Raising a custom exception
110) Raising an Exception in the Declaration Section
111) Raising an Exception Local PLSQL Block
112) Raising NO_DATA_FOUND
113) Scope Rules
114) Select into statement with exception catch statement
115) Select the first names for the Doe family from the Worker table
116) The forward slash on a line by itself says execute this procedure
117) The Lexical Set of Elements
118) The NULL Statement
119) The OTHERS Exception Handler
120) There are some restrictions on the declaration of variables
121) This is an anonymous procedure, so it has no name
122) TO_CHAR(d3,Day, Month dd, yyyy)
123) TO_CHAR(n1,999D99)
124) TO_CHAR(n2,$9G999D99)
125) TO_DATE(1102,mmddyy)
126) TO_DATE(1-1-1998,mm-dd-yyyy)
127) TO_DATE(Jan 1, 2000,mon dd, yyyy)
128) TO_NUMBER ($1,235 95,$9G999D99)
129) TO_NUMBER (123 99,999D99)
130) TOO_MANY_ROWS Exception
131) Types of more commonly used Exceptions
132) Understanding Different Exception Types
133) Update returning into
134) Update salary with stored procedure
135) UPDATE statement with variable
136) Update table and return if success
137) Update value and return affected row count
138) Use a nested block to catch exceptions from singleton SELECT
139) Use cursor variable in update statement
140) Use Declared variables in SQL statements
141) Use decode in PLSQL
142) Use LOOP to insert data to a table with loop counter variable
143) Use PLSQL to insert data to table
144) Use PLSQL variables with insert statement
145) Use RAISE_APPLICATION_ERROR to re throw exceptions
146) Use SQL%NOTFOUND after insert statement
147) Use the LOWER function to in DBMS_OUTPUT PUT_LINE
148) Use the pseudocolumn LEVEL
149) Use the ROUND function in PLSQL
150) Use TO_CHAR function in DBMS_OUTPUT PUT_LINE
151) Use TO_CHAR function in PLSQL
152) Use TO_DATE function in PLSQL
153) Use update statement in stored procedure
154) User-Defined Errors
155) Uses a PLSQL Nested Block
156) Using PRAGMA EXCEPTION_INIT
157) Using RAISE_APPLICATION_ERROR
158) Using SQLCODE and SQLERRM
159) Using SQLCODE for error code and SQLERRM for error message
160) Using TO_DATE Within PLSQL
161) Using two single-quotes print the string correctly
162) Using WHEN OTHERS clause
163) V_value_nr
164) Variables and Types
165) When the exception occurs, program control passes to the EXCEPTION block where the WHEN clause is examined for a matchi
166) Writing a simple program
167) Year 2000 problems Note the effect of using rr instead of yy
168) ZERO_DIVIDE Exception